The University of Texas

CS 327e

Spring 2003

Lab 2 Solution

a) Problems 1, 2, 3 on pg. 163 of Chapter 3 from the text (Using Chen model notation).

The completed Chen ERD is shown in Figure P3.3. Note that there are two relationships between DEPARTMENT and EMPLOYEE.

Figure P3.3 The Completed Chen ERD for Problems 1-3

Discussion: Note that the ERD shown in Figure P3.3 reflects several useful features that become especially important when the design is implemented. For example:

• The ASSIGN entity is shown to be optional to the PROJECT. This decision makes sense from a practical perspective, because it lets you create a new project record without having to create a new assignment record. (If a new project is started, there will not yet be any assignments.)

• The relationship expressed by “DEPARTMENT employs EMPLOYEE” is shown as mandatory on the EMPLOYEE side. This means that a DEPARTMENT must have at least one EMPLOYEE in order to have departmental status. However, DEPARTMENT is optional to EMPLOYEE, so an employee can be entered without entering a departmental FK value. If the existence of nulls is not acceptable, you can create a “No assignment” record in the DEPARTMENT table, to be referenced in the EMPLOYEE table if an employee is not assigned to a department.

• Note also the implications of the 1:1 “EMPLOYEE manages DEPARTMENT” relationship. The flip side of this relationship is that “each DEPARTMENT is managed by one EMPLOYEE”. (This latter relationship is shown as mandatory in the ERD. That is, each department must be managed by an employee!) Therefore, one of the EMPLOYEE table’s PK values must appear as the FK value in the DEPARTMENT table. (Because this is a 1:1 relationship, the index property of the EMP_NUM FK in the DEPARTMENT table must be set to “unique.”)

• Although you ought to approach a 1:1 relationship with caution – most 1:1 relationships are the result of a misidentification of attributes as entities – the 1:1 relationships reflected in the “EMPLOYEE manages DEPARTMENT” and “EMPLOYEE runs DIVISION” are appropriate. These 1:1 relationships avoid the data redundancies you would encounter if you duplicated employee data – such a names, phones, and e-mail addresses – in the DIVISION and DEPARTMENT entities.

b) Problems 1, 2, 3, 4 on pg. 201-202 of Chapter 4 from the text (Using Chen model notation).

The solution to both problem 1 and 2 is shown in Figure P4.1&2.

Figure P4.1&2 The Dependency Diagrams for Problems 1 and 2

To illustrate the effect of Problem 3's complete decomposition, we have shown Problem 1's dependency diagram again in Figure P4.3.

Figure P4.3 The Dependency Diagram for Problem 4.3

The ER-Diagram is shown below: (note: This diagram is drawn using ALLFusion Data modeler (formerly known as ERWin), a commercial product produced by Computer Associates company.

c) Problems 15, 16, 17 on pg. 205 of Chapter 4 (Using Chen model notation).

To fit the attributes more easily into the initial dependency diagram, we have shortened their names, making sure that we have adhered to the naming conventions. Note that DIN_CODE in Figure P4.18 does not determine DIN_ATTEND; just because a dinner is offered does not mean that it is attended.

Figure P4.15 The Dependency Diagram for Problem 15

16. Break up the dependency diagram you drew in problem 15 to produce dependency diagrams that are in 3NF. (Hint: You might have to create a few new attributes. Also, make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.)

Actually, there is no way to prevent the existence of multi-valued attributes by merely following normalization rules. Instead, knowledge of E-R modeling concepts will help define the environment in which the multi-valued attributes are dealt with. Although we keep repeating the message, it is worth repeating: normalization and modeling fit within the same design spectrum and they take place concurrently as the definition of entities and their attributes take place.

The design process can be described thus:

¨ Define entities, attributes, and relationships and model them.

¨ Normalize.

¨ Redesign based on the normalization outcomes and the evaluation of the design's ability to meet transaction and information requirements.

¨ Normalize the results and evaluate the normal forms until the process has yielded a stable design, implementation, and applications development environment.

Such a process will yield the dependency diagrams shown in Figure P4.19. In this case, it hardly seems practical to eliminate the 2NF condition displayed by MEMBER. After all, zip codes tend to be thought of as part of the address. Worse, the elimination of the MEMBER's 2NF condition would require the creation of a ZIPCODE table, with ZIP_CODE as the foreign key in the MEMBER table. Such a solution would merely add complexity without adding functionality.

Figure P4.16 The Dependency Diagram for Problem 16

CORRECTION: In the INVITATION table, the PK should be the composite PK: DIN_CODE, MEM_NUM and the INVITE_NUM should be eliminated.

It easy to see the functionality of the decomposition shown in Figure P4.19. For example, the (composite) INVITATION and DINNER entities make it possible to track who was sent an invitation on what date (INVITE_DATE) to a dinner to be held at some specified date (DIN_DATE), what dinner (DIN_CODE) would be served on that date, who (MEM_NUM) accepted the invitation (INVITE_ACCEPT), and who actually attended (INVITE_ATTEND. The INVITE_ACCEPT attribute would be a simple Y/N, as would be the INVITE_ATTEND. To avoid nulls, the default values for INVITE_ACCEPT and INVITE_ATTEND could be set to N. Getting the number of acceptances for a given dinner by a given date would be simple, thus enabling the catering service to plan the dinner better.

17. Using the results of problem 16, draw the E-R diagram.

Figure P4.17 The ERD Problems 17